

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.google.common.base.CaseFormat;
import org.apache.commons.text.CaseUtils;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author wengzw
 * @date 2022/5/14 18:39
 */
public class ExcelListener extends AnalysisEventListener<DataExcel> {

    // 需要设置的模式
    private static String model = "BYDBC_INSPECT";

    // 设置的表名
    private static String tableName = "ATTACHFILE_LIST";

    //实体名
    private static String EntityName = "TestEnty";

    //创建list集合封装最终数据
    private static List<DataExcel> list = new ArrayList<>();

    //定义输出的文件的位置
    private static String fileOutPlace = "D:/test.txt";

    //一行一行进行读取
    @Override
    public void invoke(DataExcel dataExcel, AnalysisContext analysisContext) {
        list.add(dataExcel);
    }

    //读取excel表头信息
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头信息：" + headMap);
    }


    //读取完成后执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        createTableAndNote();
//        insertMapperXml();
//        annotionApi();
        System.out.println("解析完成");
    }

    public static void annotionApi() {
        List<String> list1 = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            String temp = "@ApiImplicitParam(name = ";
            temp += "\"" + list.get(i).getName() + "\"" + ", value = " + "\"" + list.get(i).getNote() + "\"" + "),";
            list1.add(temp);
        }
        for (int i = 0; i < list1.size(); i++) {
            System.out.println(list1.get(i));
        }
    }

    //生成插入语句 xml
    public static void insertMapperXml() {
        List<String> list1 = new ArrayList<>();
        TestEntity testEntity = new TestEntity();
        Field[] fields = testEntity.getClass().getDeclaredFields();
        String tempInsert = "INSERT INTO " + "\"" + model + "\"" + "." + "\"" + tableName + "\"" + "(";
        //表中字段
        for (int i = 1; i < list.size(); i++) {
            tempInsert += "\"" + list.get(i).getName() + "\"" + ",";
        }
        tempInsert += tempInsert.substring(0, tempInsert.length() - 1) + ")" + "\n" + "VALUES" + "\n";
        tempInsert += "<foreach collection=\"list\" item=" + "\"" + EntityName + "\"" + " separator=" + "\"" + "," + "\"" + ">" + "\n" + "(";
        for (int i = 0; i < fields.length; i++) {
            fields[i].setAccessible(true);
            if (fields[i].getAnnotation(NotRead.class) != null) {
                continue;
            }
            list1.add("#{" + EntityName + "." + fields[i].getName() + "},");
        }
        String s = "";
        for (int i = 0; i < list1.size(); i++) {
            if (i % 5 != 0) {
                s += list1.get(i);
            } else {
                s += list1.get(i) + "\n";
            }

        }
        tempInsert += s;
        tempInsert = tempInsert.substring(0, tempInsert.length() - 1) + ")" + "\n" + "</foreach>" + "\n" + "</insert>";
        //输出到本地文件
        File file = new File(fileOutPlace);
        BufferedWriter bw = null;
        try {
            bw = new BufferedWriter(new FileWriter(file));
            bw.write(tempInsert);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bw.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    //创建表并添上备注
    public void createTableAndNote() {
        String createTable = "CREATE TABLE \"" + model + "\"" + "." + "\"" + tableName + "\"" + "(" + "\n";
        List<String> list1 = new ArrayList<>();
        //第一个字段默认为主键
        createTable += "\"" + list.get(0).getName().toUpperCase() + "\"" + " VARCHAR(200)" + " NOT NULL," + "\n";
        for (int i = 1; i < list.size(); i++) {
            createTable += "\"" + list.get(i).getName().toUpperCase() + "\"" + " VARCHAR(200)," + "\n";
            if (list.get(i).getNote() != null) {
                String note = "COMMENT ON COLUMN \"" + model + "\"" + "." + "\"" + tableName + "\"" + "."  + "\"" + list.get(i).getName().toUpperCase() + "\"" + " IS";
                note += " \'" + list.get(i).getNote() + "\'" + ";";
                list1.add(note);
            }
        }
        createTable += "NOT CLUSTER PRIMARY KEY(\"ID\")) STORAGE(ON \"BYDBC\", CLUSTERBTR);";
        System.out.println(createTable);
        list1.forEach(item -> System.out.println(item));
        BufferedWriter bw = null;
        //输出到本地文件
        File file = new File(fileOutPlace);
        try {
            bw = new BufferedWriter(new FileWriter(file));
            bw.write(createTable);
            bw.write("\n");
            for (int i = 0; i < list1.size(); i++) {
                bw.write(list1.get(i));
                bw.write("\n");
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bw.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

//    public static void main(String[] args) {
////        humpToUnderline("");
//        String s = CaseUtils.toCamelCase("USERNAMEPASSWORD", true, '_');
//        System.out.println(s);
//        System.out.println(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, "testdata"));
//    }
//    /**
//     * 驼峰转下划线
//     * @param str   目标字符串
//     * @return: java.lang.String
//     */
//    public static String humpToUnderline(String str) {
//        String regex = "([A-Z])";
//        Matcher matcher = Pattern.compile(regex).matcher(str);
//        while (matcher.find()) {
//            String target = matcher.group();
//            str = str.replaceAll(target, "_"+target.toLowerCase());
//        }
//        return str;
//    }
}
